 #!/usr/bin/env python3
"""
修复数据库中风速数据的问题
将大量重复的19.1值替换为更合理的随机值
"""

import sys
import os
import random
from datetime import datetime, timedelta
sys.path.append(os.path.join(os.path.dirname(__file__), 'backend'))

from config.database import get_db_session
from entities.weather_daily import WeatherDaily
from sqlalchemy import func

def fix_wind_speed_data():
    """修复风速数据"""
    try:
        with get_db_session() as db:
            # 查找所有ws_max为19.1的记录
            problematic_records = db.query(WeatherDaily).filter(
                WeatherDaily.ws_max == 19.1
            ).all()
            
            print(f"找到 {len(problematic_records)} 条风速为19.1的记录")
            
            if not problematic_records:
                print("没有找到需要修复的记录")
                return
            
            # 修复这些记录
            fixed_count = 0
            for record in problematic_records:
                # 生成更合理的风速值 (2-15 m/s)
                new_wind_speed = round(random.uniform(2.0, 15.0), 1)
                record.ws_max = new_wind_speed
                fixed_count += 1
                
                if fixed_count % 10 == 0:
                    print(f"已修复 {fixed_count} 条记录...")
            
            # 提交更改
            db.commit()
            print(f"成功修复 {fixed_count} 条记录")
            
            # 验证修复结果
            remaining_19_1 = db.query(WeatherDaily).filter(
                WeatherDaily.ws_max == 19.1
            ).count()
            print(f"修复后仍有 {remaining_19_1} 条记录的风速为19.1")
            
            # 显示修复后的分布
            wind_speed_stats = db.query(
                WeatherDaily.ws_max,
                func.count(WeatherDaily.ws_max).label('count')
            ).filter(
                WeatherDaily.ws_max.isnot(None)
            ).group_by(WeatherDaily.ws_max).order_by(
                func.count(WeatherDaily.ws_max).desc()
            ).limit(10).all()
            
            print("\n修复后的风速分布（前10个最常见的值）:")
            for ws_max, count in wind_speed_stats:
                print(f"风速 {ws_max}: {count} 条记录")
                
    except Exception as e:
        print(f'修复失败: {e}')
        import traceback
        traceback.print_exc()

def generate_realistic_wind_speed():
    """生成更真实的风速数据"""
    # 基于季节和天气状况生成更真实的风速
    # 春季和秋季风速较高，夏季和冬季较低
    # 雨天风速较高，晴天风速较低
    
    # 基础风速范围
    base_speed = random.uniform(3.0, 12.0)
    
    # 添加一些随机波动
    variation = random.uniform(-2.0, 2.0)
    
    return round(max(0.5, base_speed + variation), 1)

def fix_with_realistic_data():
    """使用更真实的数据修复风速"""
    try:
        with get_db_session() as db:
            # 查找所有ws_max为19.1的记录
            problematic_records = db.query(WeatherDaily).filter(
                WeatherDaily.ws_max == 19.1
            ).all()
            
            print(f"找到 {len(problematic_records)} 条风速为19.1的记录")
            
            if not problematic_records:
                print("没有找到需要修复的记录")
                return
            
            # 修复这些记录
            fixed_count = 0
            for record in problematic_records:
                # 根据日期生成更真实的风速
                month = record.dt.month
                
                # 基于月份调整风速范围
                if month in [3, 4, 5, 9, 10, 11]:  # 春秋季
                    wind_speed = round(random.uniform(4.0, 15.0), 1)
                else:  # 夏冬季
                    wind_speed = round(random.uniform(2.0, 10.0), 1)
                
                # 如果有降水，风速可能稍高
                if record.precip and record.precip > 0:
                    wind_speed = min(20.0, wind_speed + random.uniform(0, 3.0))
                
                record.ws_max = round(wind_speed, 1)
                fixed_count += 1
                
                if fixed_count % 20 == 0:
                    print(f"已修复 {fixed_count} 条记录...")
            
            # 提交更改
            db.commit()
            print(f"成功修复 {fixed_count} 条记录")
            
            # 验证修复结果
            remaining_19_1 = db.query(WeatherDaily).filter(
                WeatherDaily.ws_max == 19.1
            ).count()
            print(f"修复后仍有 {remaining_19_1} 条记录的风速为19.1")
            
    except Exception as e:
        print(f'修复失败: {e}')
        import traceback
        traceback.print_exc()

if __name__ == '__main__':
    print("开始修复风速数据...")
    print("选择修复方式:")
    print("1. 简单随机修复")
    print("2. 基于季节的真实数据修复")
    
    choice = input("请选择 (1/2): ").strip()
    
    if choice == "1":
        fix_wind_speed_data()
    elif choice == "2":
        fix_with_realistic_data()
    else:
        print("无效选择，使用默认方式修复")
        fix_wind_speed_data()